<?

function SelectTmpMemotrans() {
	global $DBConnection,$DBMysql;
            $TEMP = "TEMPORARY";
			//$current_date = date("Y-m-d",mktime (0,0,0,GetParam("m",""),1,GetParam("y","")));
			list($day,$month,$year) = explode("/",GetParam("transDate",""));
			$current_date = "$year-$month-$day";

			$SQL = "DROP TABLE IF EXISTS `ais_hutang_terhutang`";
			$DBConnection->QueryNoResult($SQL,$DBMysql);

			$SQL = "
					CREATE $TEMP TABLE IF NOT EXISTS `ais_hutang_terhutang` (
					  `ktrasl` varchar(5) default NULL,
					  `tglbkt` date,
					  `nobkt` varchar(16) default NULL,	
                                          `nobkta` varchar(16) default NULL,
                                          `skada` varchar(16) default NULL,
                                          `dk` varchar(16) default NULL,
					  `debtur` varchar(16) default NULL,
					  `debnam` varchar(32) default NULL,
					  `uraian` text default NULL,
					  `curr` varchar(4) default NULL,
					  `jumlah` varchar(32) default NULL,
					  `cr_term` tinyint(4)default NULL,
					  `aging` varchar(4)default NULL
					)
				   ";
			$DBConnection->QueryNoResult($SQL,$DBMysql);
			//print mysql_error();

			$SQL = "
					INSERT INTO ais_hutang_terhutang
                                        SELECT ais_hutang.KTRASL, TGLBKT, NOBKT,NOBKTA, SKADA, DK, DEBTUR, DEBNAM, URAIAN, CURR, JUMLAH,IFNULL(TOP,0) AS TOP, 
                                          IF('$current_date' > ADDDATE(tglbkt, INTERVAL 60 + tblvendor.TOP DAY), '>60', 
                                          IF('$current_date' >= ADDDATE(tglbkt, INTERVAL 60 + tblvendor.TOP DAY), '<=60', 
                                          IF('$current_date' > ADDDATE(tglbkt, INTERVAL 30 + tblvendor.TOP DAY), '>30', 
                                          IF('$current_date' <= ADDDATE(tglbkt, INTERVAL tblvendor.TOP DAY), tblvendor.TOP, '<=30')))) AS aging
                                        FROM ais_hutang
                                          LEFT JOIN tblvendor ON (ais_hutang.debtur = tblvendor.kdvendor)
                                          WHERE srt='1' AND ais_hutang.tglbkt<='$current_date' AND tblvendor.kdvendor='".GetParam("debtur","")."'
				   ";
			if (GetParam("office","")) 
				$SQL .= " AND ais_hutang.ktrasl = '".GetParam("office","")."'";
			$SQL .= " order by nobkt";

			$DBConnection->QueryNoResult($SQL,$DBMysql);
            //print $SQL;die();

			$SQL = "DROP TABLE IF EXISTS `ais_hutang_terbayar`";
			$DBConnection->QueryNoResult($SQL,$DBMysql);

			$SQL = "
					CREATE $TEMP TABLE IF NOT EXISTS `ais_hutang_terbayar` (
					  `nobkt` varchar(16) default NULL,					  
					  `nobkta` varchar(16) default NULL,
                      `bayar` varchar(32) default NULL
					)
				   ";
			$DBConnection->QueryNoResult($SQL,$DBMysql);

			$SQL = "
					INSERT INTO ais_hutang_terbayar
					SELECT ais_hutang.NOBKT, ais_hutang.NOBKTA, SUM(ais_hutang.JUMLAH) AS BAYAR
					FROM ais_hutang
                    LEFT JOIN tblvendor ON (ais_hutang.debtur = tblvendor.kdvendor)
                    WHERE srt='2' AND ais_hutang.tglbkt<='$current_date' AND ais_hutang.ktrasl = '".GetParam("office","")."' AND tblvendor.KDTYPE='".GetParam("kdtype","")."'
                    group by nobkta
                    order by nobkta
				   ";
                        //print $SQL;die();
			$DBConnection->QueryNoResult($SQL,$DBMysql);

			$SQL = "DROP TABLE IF EXISTS `ais_hutang_sisa`";
			$DBConnection->QueryNoResult($SQL,$DBMysql);

			$SQL = "
					CREATE $TEMP TABLE IF NOT EXISTS `ais_hutang_sisa` (
					  `ktrasl` varchar(5) default NULL,
					  `tglbkt` date,
					  `nobkt` varchar(16) default NULL,
                      `nobkta` varchar(16) default NULL,
                      `skada` varchar(16) default NULL,
                      `dk` varchar(16) default NULL,                                          
					  `debtur` varchar(16) default NULL,
					  `debnam` varchar(32) default NULL,
					  `uraian` text default NULL,
					  `curr` varchar(4) default NULL,
                      `cr_term` tinyint(4)default NULL,
					  `jumlah` varchar(32)default NULL,
                      `aging` varchar(5)default NULL
					)
				   ";
			$DBConnection->QueryNoResult($SQL,$DBMysql);

                        $SQL = "
                                        INSERT INTO ais_hutang_sisa
                                        SELECT 
                                        ais_hutang_terhutang.ktrasl,
                                        ais_hutang_terhutang.tglbkt,
                                        ais_hutang_terhutang.nobkt,
                                        ais_hutang_terhutang.nobkta,
                                        ais_hutang_terhutang.skada,
                                        ais_hutang_terhutang.dk,
                                        ais_hutang_terhutang.debtur,
                                        ais_hutang_terhutang.debnam,
                                        ais_hutang_terhutang.uraian,
                                        ais_hutang_terhutang.curr,
                                        ifnull(ais_hutang_terhutang.cr_term, 0) AS top,
                                        ifnull(ais_hutang_terhutang.jumlah,0)-ifnull( ais_hutang_terbayar.bayar,0) as jumlahcurr,
                                        ais_hutang_terhutang.aging
                                        FROM
                                        ais_hutang_terhutang
                                        LEFT OUTER JOIN  ais_hutang_terbayar ON (ais_hutang_terhutang.nobkta =  ais_hutang_terbayar.nobkta)
                                        WHERE
                                        (ifnull( ais_hutang_terbayar.nobkt, '') = '') OR (ifnull(ais_hutang_terhutang.jumlah,0)- ifnull(ais_hutang_terbayar.bayar,0)) <> 0
                                        ORDER BY debtur,nobkt
                        ";
                        //print $SQL;
			$DBConnection->QueryNoResult($SQL,$DBMysql);
}
?>